package cn.iocoder.yudao.module.product.spu.dal.mysql;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import cn.iocoder.yudao.framework.business.basic.dao.mapper.CoreMapper;
import cn.iocoder.yudao.framework.business.basic.dao.query.CoreLambdaQueryWrapper;
import cn.iocoder.yudao.framework.common.pojo.PageParam;
import cn.iocoder.yudao.framework.common.pojo.PageResult;
import cn.iocoder.yudao.framework.datapermission.core.annotation.DataPermission;
import cn.iocoder.yudao.module.api.product.spu.enums.ProductSpuRecommendEnum;
import cn.iocoder.yudao.module.api.product.spu.enums.ProductSpuSortFieldEnum;
import cn.iocoder.yudao.module.api.product.spu.enums.ProductSpuStatusEnum;
import cn.iocoder.yudao.module.api.product.spu.enums.ProductSpuTabTypeEnum;
import cn.iocoder.yudao.module.api.shop.category.eums.ShopCategoryCodeEnum;
import cn.iocoder.yudao.module.product.spu.bo.ShopIdDistanceBO;
import cn.iocoder.yudao.module.product.spu.bo.ShopIdSpuCountBO;
import cn.iocoder.yudao.module.product.spu.dal.dataobject.ProductSpuDO;
import cn.iocoder.yudao.module.product.spu.vo.AppProductSpuPageReqVO;
import cn.iocoder.yudao.module.product.spu.vo.ProductSpuPageReqVO;
import com.baomidou.mybatisplus.core.conditions.update.LambdaUpdateWrapper;
import jakarta.annotation.Nullable;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.*;
import java.util.stream.Collectors;

import static cn.iocoder.yudao.framework.util.HttpUtils.queryStringToLongs;

@Mapper
public interface ProductSpuMapper extends CoreMapper<ProductSpuDO> {

    @Select("SELECT * FROM product_spu WHERE id = #{id}")
    ProductSpuDO selectByIdIncludeDeleted(@Param("id") Long id);

    /**
     * 获取产品 SPU 分页列表数据
     *
     * @param reqVO 分页请求参数
     * @return 产品 SPU 分页列表数据
     */
    default PageResult<ProductSpuDO> selectPage(ProductSpuPageReqVO reqVO) {
        Set<Long> categoryIds = new HashSet<>();
        if (reqVO.getCategoryId() != null && reqVO.getCategoryId() > 0) {
            categoryIds.add(reqVO.getCategoryId());
        }
        List<Long> categoryIdList = queryStringToLongs(reqVO.getCategoryIds());
        if (CollUtil.isNotEmpty(categoryIdList)) {
            categoryIds.addAll(categoryIdList);
        }
        CoreLambdaQueryWrapper<ProductSpuDO> queryWrapper = new CoreLambdaQueryWrapper<>();
        queryWrapper.eqIfPresent(ProductSpuDO::getType, reqVO.getType());
        queryWrapper.likeIfPresent(ProductSpuDO::getName, reqVO.getName());
        queryWrapper.eqIfPresent(ProductSpuDO::getShopId, reqVO.getShopId());
        queryWrapper.eqIfPresent(ProductSpuDO::getPlatformCategoryId, reqVO.getPlatformCategoryId());
        queryWrapper.inIfPresent(ProductSpuDO::getCategoryId, categoryIds);
        queryWrapper.eqIfPresent(ProductSpuDO::getBrandId, reqVO.getBrandId());
        queryWrapper.eqIfPresent(ProductSpuDO::getRecommend, reqVO.getRecommend());
        queryWrapper.betweenIfPresent(ProductSpuDO::getCreateTime, reqVO.getCreateTime());
        queryWrapper.orderByDesc(ProductSpuDO::getIsSticky)
                .orderByAsc(ProductSpuDO::getSort)
                .orderByDesc(ProductSpuDO::getId);
        appendTabQuery(reqVO.getTabType(), queryWrapper);
        return selectPage(reqVO, queryWrapper);
    }

    /**
     * 获取产品 SPU 分页列表数据
     *
     * @param pageParam 分页请求参数
     * @param recommend 推荐类型
     * @return 产品 SPU 分页列表数据
     */
    default PageResult<ProductSpuDO> selectPage(PageParam pageParam, ProductSpuRecommendEnum recommend) {
        CoreLambdaQueryWrapper<ProductSpuDO> queryWrapper = new CoreLambdaQueryWrapper<>();
        queryWrapper.orderByDesc(ProductSpuDO::getIsSticky)
                .orderByDesc(ProductSpuDO::getRecommend)
                .orderByAsc(ProductSpuDO::getSort)
                .orderByDesc(ProductSpuDO::getId);
        appendRecommendQuery(recommend.getValue(), queryWrapper);
        return selectPage(pageParam, queryWrapper);
    }

    /**
     * 查询触指定状态的 SPU 数量
     *
     * @param type 产品类型，如：mall、food
     * @return 指定状态的 SPU 数量
     */
    @DataPermission(enable = false)
    default Long selectCount(@Nullable Long shopId, @Nullable String type, Integer status) {
        CoreLambdaQueryWrapper<ProductSpuDO> queryWrapper = new CoreLambdaQueryWrapper<>();
        queryWrapper.eqIfPresent(ProductSpuDO::getShopId, shopId);
        queryWrapper.eqIfPresent(ProductSpuDO::getType, type);
        queryWrapper.eq(ProductSpuDO::getStatus, status);
        return selectCount(queryWrapper);
    }

    /**
     * 查询已售罄的 SPU 数量
     *
     * @return 已售罄的 SPU 数量
     */
    @DataPermission(enable = false)
    default Long selectSoldOutCount(@Nullable Long shopId, @Nullable String type) {
        CoreLambdaQueryWrapper<ProductSpuDO> queryWrapper = new CoreLambdaQueryWrapper<>();
        queryWrapper.eqIfPresent(ProductSpuDO::getShopId, shopId);
        queryWrapper.eqIfPresent(ProductSpuDO::getType, type);
        queryWrapper.eq(ProductSpuDO::getStock, 0);
        return selectCount(queryWrapper);
    }

    /**
     * 查询触发警戒库存的 SPU 数量
     *
     * @return 触发警戒库存的 SPU 数量
     */
    @DataPermission(enable = false)
    default Long selectAlertStockCount(@Nullable Long shopId, @Nullable String type) {
        CoreLambdaQueryWrapper<ProductSpuDO> queryWrapper = new CoreLambdaQueryWrapper<>();
        queryWrapper.eqIfPresent(ProductSpuDO::getShopId, shopId);
        queryWrapper.eqIfPresent(ProductSpuDO::getType, type);
        // 库存小于等于警戒库存
        queryWrapper.le(ProductSpuDO::getStock, ProductSpuTabTypeEnum.ALERT_STOCK.getValue())
                // 如果库存触发警戒库存且状态为回收站的话则不计入触发警戒库存的个数
                .notIn(ProductSpuDO::getStatus, ProductSpuStatusEnum.RECYCLE.getStatus());
        return selectCount(queryWrapper);
    }

    /**
     * 获得产品 SPU 分页
     */
    @DataPermission(enable = false)
    default PageResult<ProductSpuDO> selectPage(AppProductSpuPageReqVO pageReqVO, Set<Long> shopIds, Set<Long> disabledShopIds, Set<Long> platformCategoryIds, Set<Long> productCategoryIds) {
        CoreLambdaQueryWrapper<ProductSpuDO> queryWrapper = new CoreLambdaQueryWrapper<>();
        queryWrapper.eqIfPresent(ProductSpuDO::getRecommend, pageReqVO.getRecommend());
        queryWrapper.eqIfPresent(ProductSpuDO::getIsIntegral, pageReqVO.getIsIntegral());
        // 生活商圈
        queryWrapper.in(Boolean.TRUE.equals(pageReqVO.getO2o()), ProductSpuDO::getType, ShopCategoryCodeEnum.getO2oCodes());
        // 店铺
        queryWrapper.inIfPresent(ProductSpuDO::getShopId, shopIds);
        queryWrapper.notIn(CollUtil.isNotEmpty(disabledShopIds), ProductSpuDO::getShopId, disabledShopIds);
        // 产品
        queryWrapper.inIfPresent(ProductSpuDO::getId, pageReqVO.getIds());
        // 行业类目
        queryWrapper.inIfPresent(ProductSpuDO::getPlatformCategoryId, platformCategoryIds);
        // 产品分类
        queryWrapper.inIfPresent(ProductSpuDO::getCategoryId, productCategoryIds);
        // 菜品属性
        queryWrapper.eqIfPresent(ProductSpuDO::getDishAttribute, pageReqVO.getDishAttribute());
        if (StrUtil.isNotBlank(pageReqVO.getKeyword())) {
            // 关键字匹配产品名称和简介
            queryWrapper.and(e -> e.like(ProductSpuDO::getName, pageReqVO.getKeyword())
                    .or().like(ProductSpuDO::getDescription, pageReqVO.getKeyword()));
        }
        if (StrUtil.isNotBlank(pageReqVO.getTabType())) {
            // 商家用户：自定义状态及库存
            appendTabQuery(pageReqVO.getTabType(), queryWrapper);
        } else {
            // 普通用户：上架状态且有库存
            queryWrapper.eq(ProductSpuDO::getStatus, ProductSpuStatusEnum.ENABLE.getStatus())
                    .ge(ProductSpuDO::getStock, 0);
        }
        // 排序逻辑
        if (StrUtil.equals(pageReqVO.getSortField(), String.valueOf(ProductSpuSortFieldEnum.SALES_COUNT.getValue())) || StrUtil.equalsIgnoreCase(pageReqVO.getSortField(), ProductSpuSortFieldEnum.SALES_COUNT.name())) {
            queryWrapper.last(String.format(" ORDER BY (sales_count + virtual_sales_count) %s",
                    pageReqVO.getSortAsc() ? "ASC" : "DESC"));
        } else if (StrUtil.equals(pageReqVO.getSortField(), String.valueOf(ProductSpuSortFieldEnum.PRICE.getValue())) || StrUtil.equalsIgnoreCase(pageReqVO.getSortField(), ProductSpuSortFieldEnum.PRICE.name())) {
            queryWrapper.orderBy(true, pageReqVO.getSortAsc(), ProductSpuDO::getPrice);
        } else if (StrUtil.equals(pageReqVO.getSortField(), String.valueOf(ProductSpuSortFieldEnum.CREATE_TIME.getValue())) || StrUtil.equalsIgnoreCase(pageReqVO.getSortField(), ProductSpuSortFieldEnum.CREATE_TIME.name())) {
            queryWrapper.orderBy(true, pageReqVO.getSortAsc(), ProductSpuDO::getCreateTime);
        } else if (StrUtil.equals(pageReqVO.getSortField(), String.valueOf(ProductSpuSortFieldEnum.DEFAULT.getValue())) || StrUtil.equalsIgnoreCase(pageReqVO.getSortField(), ProductSpuSortFieldEnum.DEFAULT.name())) {
            queryWrapper.orderByDesc(ProductSpuDO::getIsSticky)
                    .orderByDesc(ProductSpuDO::getRecommend)
                    .orderByAsc(ProductSpuDO::getSort)
                    .orderByDesc(ProductSpuDO::getSalesCount)
                    .orderByAsc(ProductSpuDO::getPrice)
                    .orderByDesc(ProductSpuDO::getId);
        } else if (StrUtil.equals(pageReqVO.getSortField(), String.valueOf(ProductSpuSortFieldEnum.RANDOM.getValue())) || StrUtil.equalsIgnoreCase(pageReqVO.getSortField(), ProductSpuSortFieldEnum.RANDOM.name())) {
            queryWrapper.last(" ORDER BY RAND()");
        } else {
            queryWrapper.last(" ORDER BY is_sticky DESC, (sales_count + virtual_sales_count) DESC, id DESC");
        }
        return selectPage(pageReqVO, queryWrapper);
    }

    /**
     * 更新产品 SPU 库存
     *
     * @param id        产品 SPU 编号
     * @param incrCount 增加的库存数量
     */
    default void updateStock(Long id, Integer incrCount) {
        LambdaUpdateWrapper<ProductSpuDO> updateWrapper = new LambdaUpdateWrapper<ProductSpuDO>()
                // 负数，所以使用 + 号
                .setSql(" stock = stock +" + incrCount)
                .eq(ProductSpuDO::getId, id);
        update(null, updateWrapper);
    }

    /**
     * 更新产品 SPU 销量
     *
     * @param id        产品 SPU 编号
     * @param incrCount 增加的销量数量
     */
    default void updateSalesCount(Long id, Integer incrCount) {
        LambdaUpdateWrapper<ProductSpuDO> updateWrapper = new LambdaUpdateWrapper<ProductSpuDO>()
                // 负数，所以使用 + 号
                .setSql(" sales_count = sales_count +" + incrCount)
                .eq(ProductSpuDO::getId, id);
        update(null, updateWrapper);
    }

    /**
     * 添加 Tab 选项的查询条件
     *
     * @param tabType 页签类型，参阅 {@link ProductSpuTabTypeEnum}
     * @param query   查询条件
     */
    static void appendTabQuery(String tabType, CoreLambdaQueryWrapper<ProductSpuDO> query) {
        if (StrUtil.isBlank(tabType)) {
            tabType = ProductSpuTabTypeEnum.ALL.name();
        }
        // 出售中产品
        if (StrUtil.equalsIgnoreCase(ProductSpuTabTypeEnum.FOR_SALE.name(), tabType)) {
            query.eqIfPresent(ProductSpuDO::getStatus, ProductSpuStatusEnum.ENABLE.getStatus());
        }
        // 已下架/仓库中产品
        else if (StrUtil.equalsIgnoreCase(ProductSpuTabTypeEnum.IN_WAREHOUSE.name(), tabType)) {
            query.eqIfPresent(ProductSpuDO::getStatus, ProductSpuStatusEnum.DISABLE.getStatus());
        }
        // 已售空产品
        else if (StrUtil.equalsIgnoreCase(ProductSpuTabTypeEnum.SOLD_OUT.name(), tabType)) {
            query.eqIfPresent(ProductSpuDO::getStock, 0);
        }
        // 警戒库存
        else if (StrUtil.equalsIgnoreCase(ProductSpuTabTypeEnum.ALERT_STOCK.name(), tabType)) {
            query.le(ProductSpuDO::getStock, ProductSpuTabTypeEnum.ALERT_STOCK.getValue())
                    // 如果库存触发警戒库存且状态为回收站的话则不在警戒库存列表展示
                    .notIn(ProductSpuDO::getStatus, ProductSpuStatusEnum.RECYCLE.getStatus());
        }
        // 回收站
        else if (StrUtil.equalsIgnoreCase(ProductSpuTabTypeEnum.RECYCLE_BIN.name(), tabType)) {
            query.eqIfPresent(ProductSpuDO::getStatus, ProductSpuStatusEnum.RECYCLE.getStatus());
        }
    }

    /**
     * 添加推荐类型的查询条件
     *
     * @param recommend 推荐类型，参阅 {@link ProductSpuRecommendEnum}
     * @param query     查询条件
     */
    static void appendRecommendQuery(Integer recommend, CoreLambdaQueryWrapper<ProductSpuDO> query) {

        // 热卖
        if (ObjectUtil.equals(ProductSpuRecommendEnum.HOT.getValue(), recommend)) {
            query.eqIfPresent(ProductSpuDO::getRecommend, ProductSpuRecommendEnum.HOT.getValue());
        }
        // 精选
        if (ObjectUtil.equals(ProductSpuRecommendEnum.BEST.getValue(), recommend)) {
            query.eqIfPresent(ProductSpuDO::getRecommend, ProductSpuRecommendEnum.BEST.getValue());
        }
        // 新品
        if (ObjectUtil.equals(ProductSpuRecommendEnum.NEW.getValue(), recommend)) {
            query.eqIfPresent(ProductSpuDO::getRecommend, ProductSpuRecommendEnum.NEW.getValue());
        }
    }

    /**
     * 更新产品 SPU 浏览量
     *
     * @param id        产品 SPU 编号
     * @param incrCount 增加的数量
     */
    default void updateBrowseCount(Long id, int incrCount) {
        LambdaUpdateWrapper<ProductSpuDO> updateWrapper = new LambdaUpdateWrapper<ProductSpuDO>()
                .setSql(" browse_count = browse_count +" + incrCount)
                .eq(ProductSpuDO::getId, id);
        update(null, updateWrapper);
    }

    @Select("""
            <script>
            SELECT shop_id, COUNT(1) AS spu_count
            FROM product_spu
            WHERE deleted = FALSE AND shop_id IN
            <foreach item="id" collection="shopIds" open="(" separator="," close=")">
                #{id}
            </foreach>
            GROUP BY shop_id
            </script>
            """)
    List<ShopIdSpuCountBO> selectSpuCountByShopIds(@Param("shopIds") Collection<Long> shopIds);

    @Select("""
            <script>
            SELECT si.id AS shop_id
            FROM shop_info si
            JOIN product_spu ps ON si.id = ps.shop_id
            WHERE si.deleted = FALSE AND ps.deleted = FALSE AND ps.status = 1 AND ps.platform_category_id IN
            <foreach item="id" collection="platformCategoryIds" open="(" separator="," close=")">
                #{id}
            </foreach>
            ORDER BY ps.id DESC;
            </script>
            """)
    List<Long> selectShopIdsOrderById(@Param("platformCategoryIds") Collection<Long> platformCategoryIds);

    @Select("""
            <script>
            SELECT si.id AS shop_id
            FROM shop_info si
            JOIN product_spu ps ON si.id = ps.shop_id
            WHERE si.deleted = FALSE AND ps.deleted = FALSE AND ps.status = 1 AND ps.platform_category_id IN
            <foreach item="id" collection="platformCategoryIds" open="(" separator="," close=")">
                #{id}
            </foreach>
            ORDER BY RAND();
            </script>
            """)
    List<Long> selectShopIdsOrderByRandom(@Param("platformCategoryIds") Collection<Long> platformCategoryIds);

    @Select("""
            <script>
            SELECT si.id AS shop_id
            FROM shop_info si
            JOIN product_spu ps ON si.id = ps.shop_id
            WHERE si.deleted = FALSE AND ps.deleted = FALSE AND ps.status = 1 AND ps.platform_category_id IN
            <foreach item="id" collection="platformCategoryIds" open="(" separator="," close=")">
                #{id}
            </foreach>
            ORDER BY ps.price DESC;
            </script>
            """)
    List<Long> selectShopIdsOrderByPrice(@Param("platformCategoryIds") Collection<Long> platformCategoryIds);

    @Select("""
            <script>
            SELECT si.id AS shop_id
            FROM shop_info si
            JOIN product_spu ps ON si.id = ps.shop_id
            WHERE si.deleted = FALSE AND ps.deleted = FALSE AND ps.status = 1 AND ps.platform_category_id IN
            <foreach item="id" collection="platformCategoryIds" open="(" separator="," close=")">
                #{id}
            </foreach>
            ORDER BY si.score DESC;
            </script>
            """)
    List<Long> selectShopIdsOrderByScore(@Param("platformCategoryIds") Collection<Long> platformCategoryIds);

    @Select("""
            <script>
            SELECT
                si.id AS shop_id,
                ROUND(
                    6371 * ACOS(
                        COS(RADIANS(si.latitude)) * COS(RADIANS(#{latitude})) *
                        COS(RADIANS(si.longitude) - RADIANS(#{longitude})) +
                        SIN(RADIANS(si.latitude)) * SIN(RADIANS(#{latitude}))
                    ), 2
                ) AS distance_km
            FROM shop_info si
            JOIN product_spu ps ON si.id = ps.shop_id
            WHERE si.deleted = FALSE AND ps.deleted = FALSE AND ps.status = 1 AND ps.platform_category_id IN
            <foreach item="id" collection="platformCategoryIds" open="(" separator="," close=")">
                #{id}
            </foreach>
            ORDER BY distance_km;
            </script>
            """)
    List<ShopIdDistanceBO> selectShopIdsAndDistance(@Param("platformCategoryIds") Collection<Long> platformCategoryIds, @Param("longitude") Double longitude, @Param("latitude") Double latitude);

    default PageResult<ProductSpuDO> selectPageForCryptocurrencyFullDeduction(PageParam param, Set<Long> disabledShopIds) {
        CoreLambdaQueryWrapper<ProductSpuDO> wrapper = new CoreLambdaQueryWrapper<>();
        wrapper.eq(ProductSpuDO::getStatus, ProductSpuStatusEnum.ENABLE.getStatus());
        wrapper.eq(ProductSpuDO::getType, ShopCategoryCodeEnum.MALL.getCode());
        wrapper.notIn(CollUtil.isNotEmpty(disabledShopIds), ProductSpuDO::getShopId, disabledShopIds);
        wrapper.apply("cost_price/price<=0.5");
        wrapper.last(" ORDER BY RAND()");
        return selectPage(param, wrapper);
    }

    default ProductSpuDO selectBySupplyRelatedId(Long supplyRelatedId) {
        return selectOne(ProductSpuDO::getSupplyRelatedId, supplyRelatedId);
    }

    default List<Long> selectSpuIdsBySupplyRelatedIds(Collection<Long> supplyRelatedIds) {
        if (CollUtil.isEmpty(supplyRelatedIds)) {
            return new ArrayList<>();
        }
        CoreLambdaQueryWrapper<ProductSpuDO> wrapper = new CoreLambdaQueryWrapper<>();
        wrapper.select(ProductSpuDO::getId);
        wrapper.in(ProductSpuDO::getSupplyRelatedId, supplyRelatedIds);
        List<ProductSpuDO> productSpuDOS = selectList(wrapper);
        if (CollUtil.isEmpty(productSpuDOS)) {
            return new ArrayList<>();
        }
        return productSpuDOS.stream().map(ProductSpuDO::getId).collect(Collectors.toList());
    }

}
